/* 
 * Copyright 2011 kOSMik.
 * This file is part of kOSMik.
 * 
 * kOSMik is free software: you can redistribute it and/or modify
 * it under the terms of the GNU General Public License as published by
 * the Free Software Foundation, either version 3 of the License, or
 * (at your option) any later version.
 * 
 * kOSMik is distributed in the hope that it will be useful,
 * but WITHOUT ANY WARRANTY; without even the implied warranty of
 * MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE.  See the
 * GNU General Public License for more details.
 * 
 * You should have received a copy of the GNU General Public License
 * along with kOSMik.  If not, see <http://www.gnu.org/licenses/>.
 *  
 * kOSMik:
 * Open Street Map - Editor.
 * http://code.google.com/p/swp-dv-ws2010-osm-2/
 */

package de.fuberlin.kosmik.data.osmt;

import java.io.BufferedReader;
import java.io.FileOutputStream;
import java.io.IOException;
import java.io.InputStream;
import java.io.InputStreamReader;
import java.io.OutputStream;

import android.content.Context;
import android.database.SQLException;
import android.database.sqlite.SQLiteDatabase;
import android.database.sqlite.SQLiteException;
import android.database.sqlite.SQLiteOpenHelper;
import android.util.Log;
import de.fuberlin.kosmik.StrUtils;
import de.fuberlin.kosmik.log.Constants;

/**
 * This database helper class extends the SQLiteOpenHelper to read in the
 * database scheme from the assets folder if the database not exists.
 * 
 * This class should be either correctly implemented or deprecated.
 * 
 * NO GUARANTEE FOR CORRECT FUNCTIONALITY
 * 
 * @author Johannes
 * 
 */
public class OsmtOpenHelper extends SQLiteOpenHelper {

	/**
	 * Database name for the tag database.
	 */
	static final String DATABASE_NAME = "kosmikOsmt" + ".db";
	/**
	 * Create schema.
	 */
	static final String SCHEME_NAME = "osmt_scheme.sql";

	/**
	 * Sql inserts.
	 */
	private static final String VALUE_INSERTS_NAME = "osmt_value_inserts.sql";
	private static final String KEY_INSERTS_NAME = "osmt_key_inserts.sql";
	private static final String NODE_INSERTS_NAME = "osmt_node_inserts.sql";
	private static final String FEATURE_INSERTS_NAME = "osmt_feature_inserts.sql";
	private static final String NODE_LT_INSERTS_NAME = "osmt_node_lt_inserts.sql";
	private static final String FEATURE_LT_INSERTS_NAME = "osmt_feature_lt_inserts.sql";
	private static final String BUNDLES_INSERTS_NAME = "osmt_bundle_inserts.sql";

	/**
	 * Upgrade script.
	 */
	static final String UPGRADE_NAME = "osmt_upgrade.sql";
	/**
	 * Version of database & schema.
	 */
	static final int DATABASE_VERSION = 1;
	// path the databases are usually stored in
	/**
	 * Location of data base file.
	 */
	static final String DATABASE_DEFAULT_PATH = "/data/data/%s/databases/";
	/**
	 * Package name of the application.
	 */
	static final String APP_PACKAGE = "de.fuberlin.kosmik.data.osmt";
	/**
	 * Path to database.
	 */
	static final String DATABASE_PATH = String.format(DATABASE_DEFAULT_PATH, APP_PACKAGE);

	private final Context context;

	/**
	 * Constructor.
	 * 
	 * @param context to use to open or create the database
	 */
	public OsmtOpenHelper(Context context) {
		super(context, DATABASE_NAME, null, DATABASE_VERSION);
		this.context = context;
	}

	@Override
	public void onCreate(SQLiteDatabase db) {
		installDBSchemeIntoDatabase(db);
		fillDBWithInserts(db);
	}

	@Override
	public void onUpgrade(SQLiteDatabase db, int oldVersion, int newVersion) {
		upgradeDBSchemeInDatabase(db);
	}

	@Override
	public void onOpen(SQLiteDatabase db) {
		super.onOpen(db);
		if (!db.isReadOnly()) {
			// Enable foreign key constraints
			db.execSQL("PRAGMA foreign_keys=ON;");
		}
	}

	/**
	 * Checks, if a sql statement is a valid parameter for execSql().
	 * 
	 * @param statement to check
	 * @return the statement, if valid, otherwise an empty string
	 */
	private String filterSQLStatement(String statement) {
		final boolean validStatement = !statement.matches("^--.*")
				&& !statement.matches("^BEGIN;") && !statement.matches("^COMMIT;");
		return validStatement ? statement : "";
	}

	/**
	 * Reads all sql statements from a file and execute them into the database.
	 * Assumption: A line in the file is a statement, and only one or a newline
	 * (\n)
	 * 
	 * @param sqlFile to write into the db, stored in the assets folder
	 * @param db the target database
	 */
	private void writeSQLFileIntoDatabase(String sqlFile, SQLiteDatabase db) {
		try {
			Log.d(Constants.TAG,
					String.format("try write file ./assets/%s int database ", sqlFile));

			// get source of the database sql file
			final InputStream inputStream = context.getAssets().open(sqlFile);
			Log.d(Constants.TAG, String.format("open sql file %s.", sqlFile));

			BufferedReader reader = new BufferedReader(new InputStreamReader(inputStream));
			String sqlStatement;
			db.beginTransaction();

			//
			while ((sqlStatement = reader.readLine()) != null) {
				try {
					if (StrUtils.isNotEmpty(sqlStatement)) {
						db.execSQL(sqlStatement);
						Log.d(Constants.TAG,
								String.format("execute sql statement: %s.", sqlStatement));
					}
				} catch (SQLException e) {
					Log.e(Constants.TAG,
							String.format("invalid sql statement: %s.", sqlStatement), e);
				}
			}

			db.setTransactionSuccessful();
			Log.d(Constants.TAG, String.format("sql file %s injected.", sqlFile));
		} catch (IOException e) {
			Log.e(Constants.TAG, String.format(
					"error while insert statements from file %s into database.", sqlFile), e);
			e.printStackTrace();
		} finally {
			db.endTransaction();
		}
	}

	private void installDBSchemeIntoDatabase(SQLiteDatabase db) {
		writeSQLFileIntoDatabase(SCHEME_NAME, db);
	}

	private void fillDBWithInserts(SQLiteDatabase db) {
		writeSQLFileIntoDatabase(KEY_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(VALUE_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(FEATURE_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(FEATURE_LT_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(NODE_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(NODE_LT_INSERTS_NAME, db);
		writeSQLFileIntoDatabase(BUNDLES_INSERTS_NAME, db);
	}

	private void upgradeDBSchemeInDatabase(SQLiteDatabase db) {
		writeSQLFileIntoDatabase(UPGRADE_NAME, db);
	}

	private boolean existsDatabase() {

		SQLiteDatabase sqLiteDatabase;

		final String databaseLocation = DATABASE_PATH + DATABASE_NAME;
		try {
			sqLiteDatabase = SQLiteDatabase.openDatabase(databaseLocation, null,
					SQLiteDatabase.OPEN_READONLY);
		} catch (SQLiteException e) {
			sqLiteDatabase = null;
		}
		if (sqLiteDatabase != null) {
			// the database exists
			sqLiteDatabase.close();
			Log.d(Constants.TAG, "database exists");
			return true;
		}
		// else the database does not exists
		Log.d(Constants.TAG, "database does not exists");
		return false;
	}

	/**
	 * Create database.
	 */
	public void installDBScheme() {

		// copy the db only, if there this db is not already installed
		if (!existsDatabase()) {
			try {
				Log.d(Constants.TAG, String.format(
						"try create database with scheme ./assets/%s", SCHEME_NAME));
				// we need call this method, otherwise we get a error
				// this.getReadableDatabase();

				// get source of the database scheme
				final InputStream schemeSource = context.getAssets().open(SCHEME_NAME);
				Log.d(Constants.TAG,
						String.format("open database scheme source file %s.", SCHEME_NAME));
				// path to the database file that should be created
				final String databaseFile = DATABASE_PATH + DATABASE_NAME;
				// open the destination file wherein the scheme has to be copied
				final OutputStream schemeDest = new FileOutputStream(databaseFile);
				Log.d(Constants.TAG, String.format("open database scheme destination file %s.",
						databaseFile));

				// copy the file from source to dest
				byte[] buffer = new byte[1024];
				int readedBytes;
				while ((readedBytes = schemeSource.read(buffer)) > 0) {
					schemeDest.write(buffer, 0, readedBytes);
				}
				Log.d(Constants.TAG, String.format("copied database scheme: %s", databaseFile));

				// we are neat and tidy up
				schemeDest.flush();
				schemeDest.close();
				schemeSource.close();
				Log.d(Constants.TAG, String.format(
						"database %s installed with scheme ./assets/%s", DATABASE_NAME,
						SCHEME_NAME));
			} catch (IOException e) {
				Log.d(Constants.TAG, "Error while install database scheme.");
				e.printStackTrace();
			}
		}
	}
}
